﻿using STHPMS1.DAL.D_Core;
using System.Data;
using ServiceStack.OrmLite;
using STHPMS1.ICore.I_Entity.Stmpms1;
using STHPMS1.Common.C_Entity.Stmpms1;
using STHPMS1.ICore.I_DAL.Repository;
using System.Collections.Generic;
using STHPMS1.ICore.I_Entity.Info;
using System.Data.SqlClient;

namespace STHPMS1.DAL.D_Repository
{
    public class E_RoadwaydetailRepository : DalBase, IE_RoadwaydetailRepository
    {
        public DataTable GetLatelyValue(string unit)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                string sql = string.Format(@"select top 1  BEGINMP,ENDMP,PCI,PQI,SRI,PSSI,RQI from E_ROADWAYDETAIL where UNIT='{0}' order by YEAR_M desc ", unit);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    da.Fill(dt);                   
                }
            } 
            return dt;
        }
        public IEnumerable<T> Get<T>(string search, IPagingInfo info)
            where T : class, IE_Roadwaydetail
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            info.TotalRecords = conn.Scalar<int>("select count(*) from E_ROADWAYDETAIL "
                                    + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
                                    + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
                                    + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
                                    + "where 1=1 " + search + "");

            string strsql = "select top " + info.PageSize + " E_ROADWAYDETAIL.* from E_ROADWAYDETAIL "
                                    + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
                                    + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
                                    + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
                                    + "where 1=1 " + search + " and ID NOT IN (select top " + info.PageSize * info.PageIndex + " E_ROADWAYDETAIL.ID from E_ROADWAYDETAIL "
                                    + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
                                    + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
                                    + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
                                    + "where 1=1 " + search + " order by E_ROADWAYDETAIL.INSPECID,E_ROADWAYDETAIL.UNIT)  order by E_ROADWAYDETAIL.INSPECID,E_ROADWAYDETAIL.UNIT";
            return conn.Select<T>(strsql);
        }

        /// <summary>
        /// 删除单元评定表中当前评定批次下的所有数据
        /// </summary>
        /// <param name="inspecid">评定名称</param>
        /// <returns></returns>
        public int Delete(string inspecid)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            string strsql = "DELETE  FROM E_ROADWAYDETAIL WHERE INSPECID = " + inspecid + "";
            return conn.ExecuteNonQuery(strsql);
            
        }

        /// <summary>
        /// 更新单元指标的值
        /// </summary>
        /// <param name="inspecid">评定名称</param>
        /// <param name="unit">单元ID</param>
        /// <param name="rdwayid">路线ID</param>
        /// <param name="colum">指标</param>
        /// <param name="values">指标值</param>
        public void Update(string inspecid, string unit, string rdwayid, string colum, double values)
        {

            IDbConnection conn = Adapter.OpenDbConnection();
            string strsql = "update E_ROADWAYDETAIL set " + colum + "=" + values + " where INSPECID='" + inspecid + "' and UNIT='" + unit + "' and RDWAYID='" + rdwayid + "'";
            conn.ExecuteNonQuery(strsql);
           
        }


        ///// <summary>
        ///// 获取路线下所有评定名称
        ///// </summary>
        ///// <param name="rdwayid">路线</param>
        ///// <returns></returns>
        //public List<string> GetInspecid(string rdwayid)
        //{
        //   IDbConnection conn = Adapter.OpenDbConnection();
        //   return conn.Column<string>("select DISTINCT INSPECID from E_ROADWAYDETAIL where RDWAYID='" + rdwayid + "'");
           
        //}

        /// <summary>
        /// 获取所有评定名称
        /// </summary>
        /// <returns></returns>
        public List<string> GetInspecid()
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            string strsql = "select DISTINCT INSPECID from E_ROADWAYDETAIL";
            return conn.Column<string>(strsql);
        }

        /// <summary>
        /// 获取评定名称下所有路线ID
        /// </summary>
        /// <param name="inspecid">评定名称</param>
        /// <returns></returns>
        public List<string> GetRdwayID(string inspecid)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            string strsql="select distinct RDWAYID from E_ROADWAYDETAIL where INSPECID='"+inspecid+"'";
            return conn.Column<string>(strsql);
        }

        
    }
}
